-------遗失率组织汇总------------------------------------------------
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=100;
set hive.exec.max.dynamic.partitions.pernode=100;
set spark.sql.crossJoin.enabled=true;
with cnt_base as (
    SELECT
   network_type
   ,date(date_time) as date_time
   ,loss_type
   ,network_code
   ,financial_center_code
   ,financial_center_name
   ,sum(cast(1/network_count as decimal(22,1))) as waybill_cnt
   ,sum(amount) as amount --赔付金额
   ,sum(if(source_type='2',cast(1/network_count as decimal(22,2)),0)) as arb_waybill_cnt  --仲裁遗失、疑似遗失票数
   ,sum(if(source_type='2',amount,0)) as arb_amountm --仲裁遗失、疑似遗失金额
   ,sum(if(source_type='1',cast(1/network_count as decimal(22,2)),0)) as claim_waybill_cnt --线上理赔遗失、疑似遗失票数
   ,sum(if(source_type='1',amount,0)) as claim_amount ----线上理赔遗失、疑似遗失金额
   ,sum(if(source_type='2' and first_type_code!='01' and second_type_code!='0112',cast(1/network_count as decimal(22,2)),0)) as arb_loss_cnt   --仲裁遗失类型-遗失票数
   ,sum(if(source_type='2' and first_type_code!='01' and second_type_code!='0112',amount,0)) as arb_loss_amount   --仲裁遗失类型-赔付金额
   ,sum(if(source_type='2' and first_type_code='01' and second_type_code='0112',cast(1/network_count as decimal(22,2)),0)) as arb_delay_cnt   --仲裁彻底延误类型-遗失票数
   ,sum(if(source_type='2' and first_type_code='01' and second_type_code='0112',amount,0)) as arb_delay_amount   --仲裁彻底延误类型-赔付金额
   ,sum(if(source_type='2' and first_type_code!='01' and second_type_code!='0112',cast(goods_value/network_count as decimal(22,2)),0)) as arb_loss_value  --仲裁遗失类型-遗失物品价值
from jms_dm.dm_sqs_loss_network_detail_mid_dt
where substr(dt,1,7) = substr('{{ execution_date | cst_ds }}',1,7)
group by network_type,network_code,date(date_time),loss_type,financial_center_code,financial_center_name
    ),
    network_date as (
SELECT
         t1.code
        ,t1.name
        ,t1.virt_code
        ,t1.virt_name
        ,t1.financial_center_code
        ,t1.financial_center_desc
        ,t1.manage_code
        ,t1.manage_name
        ,t1.network_type
        ,t2.date_mid_desc
        ,t1.provider_id
        ,t1.provider_desc --省份
        ,t1.city_id       --城市ID
        ,t1.city_desc     --城市
        ,t1.area_id       --区/县id
        ,t1.area_desc     --区/县
        ,t1.zone_code
        ,t1.zone_name
        ,t1.zone_id
        ,t1.leaf_area_id
        ,t1.area_code
        ,t1.area_name
from
    (
    select
    code,name,virt_code,virt_name,financial_center_code,financial_center_desc,manage_code,manage_name,network_type
        ,provider_id
        ,provider_desc --省份
        ,city_id       --城市ID
        ,city_desc     --城市
        ,area_id       --区/县id
        ,area_desc     --区/县
        ,zone_code
        ,zone_name
        ,zone_id
        ,leaf_area_id
        ,area_code
        ,area_name
    from jms_dim.dim_network_whole_massage
    where network_type in (2,3,4,5,6)
    and substr(code,1,1)!='B'
    and name not like '%测试%'
    and network_type !=1 --非总部
    ) t1 join
    (
    select date_mid_desc
    from jms_dim.dim_date_info_base
    where date_mid_desc between trunc('{{ execution_date | cst_ds }}','MM') and  '{{ execution_date | cst_ds }}'
    ) t2 on 1=1
    )

insert overwrite table  jms_dm.dm_sqs_loss_network_cnt_dt
select
    net.network_type  --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
     ,net.date_mid_desc as date_time --日期
     ,tt.loss_type --遗失类型：遗失、疑似遗失
     ,net.code as network_code --网点编码
     ,net.name as network_name --网点名称
     ,net.manage_code  --大区code
     ,net.manage_name --大区name
     ,nvl(net.virt_code ,net.financial_center_code) as virt_code --虚拟代理区code
     ,nvl(net.virt_name ,net.financial_center_desc) as virt_name --虚拟代理区name
     ,net.financial_center_code as financial_center_code --代理区code
     ,net.financial_center_desc as financial_center_name --代理区名称
     ,if(net.virt_code is null,0,1) as is_org_virt --是否是实际虚拟代理区
     ,nvl(tt.waybill_cnt,0) as waybill_cnt--票量
     ,nvl(op.site_ops,0) as operate_cnt --操作量
     ,net.provider_id
     ,net.provider_desc --省份
     ,net.city_id       --城市ID
     ,net.city_desc     --城市
     ,net.area_id       --区/县id
     ,net.area_desc     --区/县
     ,net.zone_code as district_code
     ,net.zone_name  as district_name
     ,arb_waybill_cnt       --仲裁遗失票数
     ,arb_amount            --仲裁遗失金额
     ,claim_waybill_cnt     --线上理赔遗失票数（线上理赔只有遗失，不需要做判断）
     ,claim_amount          --线上理赔遗失金额
     ,arb_loss_cnt          --仲裁遗失类型-遗失票数
     ,arb_loss_amount       --仲裁遗失类型-赔付金额
     ,arb_delay_cnt         --仲裁彻底延误类型-遗失票数
     ,arb_delay_amount      --仲裁彻底延误类型-赔付金额
     ,arb_loss_cnt2          --仲裁遗失类型-疑似遗失票数
     ,arb_delay_cnt2         --仲裁彻底延误类型-疑似遗失票数
     ,amount
     ,net.zone_id as district_id
     ,net.leaf_area_id  as  manager_area_id
     ,net.area_code as manager_area_code  --网管片区_责任网点3
     ,net.area_name as manager_area_name  --网管片区_责任网点3
     ,arb_loss_value2 --仲裁遗失类型-疑似遗失物品价值（被申报遗失类型物品价值）
     ,net.date_mid_desc as dt
from
    (select * from network_date where network_type in (4,5,6) ) net
     left join
    (
        select
            network_type
             ,date_time
             ,loss_type --遗失
             ,network_code
             ,waybill_cnt
             ,if(loss_type='遗失',arb_waybill_cnt,0) as arb_waybill_cnt   --仲裁遗失票数
             ,if(loss_type='遗失',arb_amountm,0) as arb_amount            --仲裁遗失金额
             ,claim_waybill_cnt                                           --线上理赔遗失票数（线上理赔只有遗失，不需要做判断）
             ,claim_amount                                                --线上理赔遗失金额
             ,if(loss_type='遗失',arb_loss_cnt,0) as arb_loss_cnt          --仲裁遗失类型-遗失票数
             ,if(loss_type='遗失',arb_loss_amount,0) as arb_loss_amount    --仲裁遗失类型-赔付金额
             ,if(loss_type='遗失',arb_delay_cnt,0) as arb_delay_cnt        --仲裁彻底延误类型-遗失票数
             ,if(loss_type='遗失',arb_delay_amount,0) as arb_delay_amount  --仲裁彻底延误类型-赔付金额
             ,if(loss_type='疑似遗失',arb_loss_cnt,0) as arb_loss_cnt2          --仲裁遗失类型-疑似遗失票数
             ,if(loss_type='疑似遗失',arb_delay_cnt,0) as arb_delay_cnt2        --仲裁彻底延误类型-疑似遗失票数
             ,sum(amount) over(partition by network_code,date_time,loss_type)   as amount --总赔付金额
             ,if(loss_type='疑似遗失',arb_loss_value,0) as arb_loss_value2        --被申报遗失类型物品价值
        from cnt_base
        where network_type in (4,5,6)
    )tt on tt.network_code=net.code and tt.date_time=net.date_mid_desc
    left join
    (
        select site_code,site_ops,dt
        from jms_dws.dws_tbl_site_scan_operation_stat_dt
        where substr(dt,1,7) = substr('{{ execution_date | cst_ds }}',1,7)
    )op on op.site_code=tt.network_code and tt.date_time=op.dt

union all

select
      net.network_type  --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
     ,net.date_mid_desc as date_time --日期
     ,tt.loss_type --遗失类型：遗失、疑似遗失
     ,net.code as network_code --网点编码
     ,net.name as network_name --网点名称
     ,net.manage_code  --大区code
     ,net.manage_name --大区name
     ,nvl(net.virt_code ,net.financial_center_code) as virt_code --虚拟代理区code
     ,nvl(net.virt_name ,net.financial_center_desc) as virt_name --虚拟代理区name
     ,net.financial_center_code as financial_center_code --代理区code
     ,net.financial_center_desc as financial_center_name --代理区名称
     ,if(net.virt_code is null,0,1) as is_org_virt --是否是实际虚拟代理区
     ,tt.waybill_cnt --票量
     ,nvl(op.site_ops,0) as operate_cnt --操作量
     ,net.provider_id
     ,net.provider_desc --省份
     ,net.city_id       --城市ID
     ,net.city_desc     --城市
     ,net.area_id       --区/县id
     ,net.area_desc     --区/县
     ,null as district_code
     ,null as district_name
     ,arb_waybill_cnt       --仲裁遗失票数
     ,arb_amount            --仲裁遗失金额
     ,claim_waybill_cnt     --线上理赔遗失票数（线上理赔只有遗失，不需要做判断）
     ,claim_amount          --线上理赔遗失金额
     ,arb_loss_cnt          --仲裁遗失类型-遗失票数
     ,arb_loss_amount       --仲裁遗失类型-赔付金额
     ,arb_delay_cnt         --仲裁彻底延误类型-遗失票数
     ,arb_delay_amount      --仲裁彻底延误类型-赔付金额
     ,arb_loss_cnt2          --仲裁遗失类型-疑似遗失票数
     ,arb_delay_cnt2         --仲裁彻底延误类型-疑似遗失票数
     ,amount                 --总赔付金额
     ,net.zone_id as district_id
     ,net.leaf_area_id      as manager_area_id
     ,net.area_code as manager_area_code  --网管片区_责任网点3
     ,net.area_name as manager_area_name  --网管片区_责任网点3
     ,arb_loss_value2                     --仲裁遗失类型-疑似遗失物品价值（被申报遗失类型物品价值）
     ,net.date_mid_desc as dt
from
    (select * from network_date where network_type=2 )net
     left join
    (
        SELECT
            network_type
             ,network_code
             ,date_time
             ,loss_type
             ,sum(waybill_cnt) as waybill_cnt
             ,sum(arb_waybill_cnt  ) as arb_waybill_cnt       --仲裁遗失票数
             ,sum(arb_amount       ) as arb_amount            --仲裁遗失金额
             ,sum(claim_waybill_cnt) as claim_waybill_cnt     --线上理赔遗失票数（线上理赔只有遗失，不需要做判断）
             ,sum(claim_amount     ) as claim_amount          --线上理赔遗失金额
             ,sum(arb_loss_cnt     ) as arb_loss_cnt          --仲裁遗失类型-遗失票数
             ,sum(arb_loss_amount  ) as arb_loss_amount       --仲裁遗失类型-赔付金额
             ,sum(arb_delay_cnt    ) as arb_delay_cnt         --仲裁彻底延误类型-遗失票数
             ,sum(arb_delay_amount ) as arb_delay_amount      --仲裁彻底延误类型-赔付金额
             ,sum(arb_loss_cnt2     ) as arb_loss_cnt2          --仲裁遗失类型-疑似遗失票数
             ,sum(arb_delay_cnt2    ) as arb_delay_cnt2         --仲裁彻底延误类型-疑似遗失票数
             ,sum(amount) as amount  --总赔付金额
             ,sum(arb_loss_value2) as arb_loss_value2  --仲裁遗失类型-疑似遗物品价值
        from
            (
                --select
                --    cast(network_type as int ) as network_type
                --     ,date_time as date_time
                --     ,loss_type
                --     ,network_code
                --     ,waybill_cnt
                --     ,if(loss_type='遗失',arb_waybill_cnt,0) as arb_waybill_cnt   --仲裁遗失票数
                --     ,if(loss_type='遗失',arb_amountm,0) as arb_amount            --仲裁遗失金额
                --     ,claim_waybill_cnt                                           --线上理赔遗失票数（线上理赔只有遗失，不需要做判断）
                --     ,claim_amount                                                --线上理赔遗失金额
                --     ,if(loss_type='遗失',arb_loss_cnt,0) as arb_loss_cnt          --仲裁遗失类型-遗失票数
                --     ,if(loss_type='遗失',arb_loss_amount,0) as arb_loss_amount    --仲裁遗失类型-赔付金额
                --     ,if(loss_type='遗失',arb_delay_cnt,0) as arb_delay_cnt        --仲裁彻底延误类型-遗失票数
                --     ,if(loss_type='遗失',arb_delay_amount,0) as arb_delay_amount  --仲裁彻底延误类型-赔付金额
                --     ,if(loss_type='疑似遗失',arb_loss_cnt,0) as arb_loss_cnt2          --仲裁遗失类型-疑似遗失票数
                --     ,if(loss_type='疑似遗失',arb_delay_cnt,0) as arb_delay_cnt2        --仲裁彻底延误类型-疑似遗失票数
                --     ,amount  --总赔付金额
                --from cnt_base
                --where network_type =2
                --union all
                select
                    2 as network_type
                     ,date_time as date_time
                     ,loss_type
                     ,financial_center_code as network_code
                     ,sum(waybill_cnt) as waybill_cnt
                     ,sum(if(loss_type='遗失',arb_waybill_cnt,0)) as arb_waybill_cnt   --仲裁遗失票数
                     ,sum(if(loss_type='遗失',arb_amountm,0)) as arb_amount            --仲裁遗失金额
                     ,sum(claim_waybill_cnt)  as claim_waybill_cnt                     --线上理赔遗失票数（线上理赔只有遗失，不需要做判断）
                     ,sum(claim_amount) as claim_amount                                --线上理赔遗失金额
                     ,sum(if(loss_type='遗失',arb_loss_cnt,0)) as arb_loss_cnt         --仲裁遗失类型-遗失票数
                     ,sum(if(loss_type='遗失',arb_loss_amount,0)) as arb_loss_amount   --仲裁遗失类型-赔付金额
                     ,sum(if(loss_type='遗失',arb_delay_cnt,0)) as arb_delay_cnt       --仲裁彻底延误类型-遗失票数
                     ,sum(if(loss_type='遗失',arb_delay_amount,0)) as arb_delay_amount --仲裁彻底延误类型-赔付金额
                     ,sum(if(loss_type='疑似遗失',arb_loss_cnt,0)) as arb_loss_cnt2     --仲裁遗失类型-疑似遗失票数
                     ,sum(if(loss_type='疑似遗失',arb_delay_cnt,0)) as arb_delay_cnt2   --仲裁彻底延误类型-疑似遗失票数
                     ,sum(amount) as amount --总赔付金额
                     ,sum(if(loss_type='疑似遗失',arb_loss_value,0)) as arb_loss_value2     --仲裁遗失类型-疑似遗失价值
                from cnt_base
                where financial_center_code is not null
                group by financial_center_code,date_time,loss_type
            )t group by network_type,network_code,date_time,loss_type
    )tt on tt.network_code=net.code and tt.date_time=net.date_mid_desc
    left join
    (
        select agent_code,sum(site_ops) as site_ops,dt
        from jms_dws.dws_tbl_site_scan_operation_stat_dt
        where substr(dt,1,7) = substr('{{ execution_date | cst_ds }}',1,7)
        and site_type=6 --网点的操作量
        group by agent_code,dt
    )op on op.agent_code=tt.network_code and tt.date_time=op.dt

    distribute by 1;

-----类型汇总-----------------------------------------------------------------------
with cnt_base as (
    select
        date_time  --统计日期 结案时间
         ,loss_type  --遗失类型
         ,network_code --网点code
         ,first_type_id    --一级异常类型id
         ,first_type_code  --一级异常类型code
         ,first_type     --一级异常类型名称(冗余)
         ,second_type_id   --二级异常类型id
         ,second_type_code --二级异常类型code
         ,second_type--二级异常类型名称(冗余)
         ,sum(waybill_no_cnt) as waybill_cnt --票量
         ,network_type
         ,financial_center_code
         ,financial_center_name
    from
        (
            select
                waybill_no
                 ,loss_type
                 ,network_code
                 ,network_name
                 ,date(date_time)  as  date_time
       ,first_type_id    --一级异常类型id
       ,first_type_code  --一级异常类型code
       ,first_type       --一级异常类型名称(冗余)
       ,second_type_id   --二级异常类型id
       ,second_type_code --二级异常类型code
       ,second_type      --二级异常类型名称(冗余)
       ,(1/network_count) as waybill_no_cnt
       ,network_type
       ,financial_center_code
       ,financial_center_name
from  jms_dm.dm_sqs_loss_network_detail_mid_dt
where substr(dt,1,7) = substr('{{ execution_date | cst_ds }}',1,7)
  and loss_type='遗失'
    )t group by    loss_type  --遗失类型
        ,network_code --网点code
        ,first_type_id    --一级异常类型id
        ,first_type_code  --一级异常类型code
        ,first_type     --一级异常类型名称(冗余)
        ,second_type_id   --二级异常类型id
        ,second_type_code --二级异常类型code
        ,second_type--二级异常类型名称(冗余)
        ,network_type
        ,financial_center_code
        ,financial_center_name
        ,date_time
    )

insert overwrite table jms_dm.dm_sqs_loss_type_cnt_dt
SELECT
    tt.date_time  --统计日期 结案时间
     ,tt.loss_type  --遗失类型
     ,net.id as network_id --网点id
     ,tt.network_code --网点code
     ,net.name as network_name --网点name
     ,tt.first_type_id    --一级异常类型id
     ,tt.first_type_code  --一级异常类型code
     ,tt.first_type     --一级异常类型名称(冗余)
     ,tt.second_type_id   --二级异常类型id
     ,tt.second_type_code --二级异常类型code
     ,tt.second_type--二级异常类型名称(冗余)
     ,tt.waybill_cnt --票量
     ,net.manage_code
     ,net.manage_name
     ,nvl(net.virt_code,net.financial_center_code) as virt_code
     ,nvl(net.virt_name,net.financial_center_desc) as virt_name
     ,net.financial_center_code
     ,net.financial_center_desc
     ,if(net.virt_code is not null,1,0) as is_org_virt
     ,net.provider_id   --省份ID
     ,net.provider_desc --省份
     ,net.city_id       --城市ID
     ,net.city_desc     --城市
     ,net.area_id       --区/县id
     ,net.area_desc     --区/县
     ,net.zone_code  as district_code
     ,net.zone_name as district_name
     ,tt.network_type --网点类型
     ,net.zone_id as district_id
     ,net.leaf_area_id  as     manager_area_id
     ,net.area_code as manager_area_code  --网管片区_责任网点3
     ,net.area_name as manager_area_name  --网管片区_责任网点3
     ,tt.date_time as dt
from
    (
        select
            date_time
             ,loss_type
             ,network_code
             ,first_type_id
             ,first_type_code
             ,first_type
             ,second_type_id
             ,second_type_code
             ,second_type
             ,waybill_cnt
             ,network_type
        from cnt_base where network_type in (4,5,6)

        union all

        select
            date_time
             ,loss_type
             ,network_code
             ,first_type_id
             ,first_type_code
             ,first_type
             ,second_type_id
             ,second_type_code
             ,second_type
             ,sum(waybill_cnt) as waybill_cnt
             ,network_type
        from
            (
                select
                    date_time
                     ,loss_type
                     ,financial_center_code as network_code
                     ,first_type_id
                     ,first_type_code
                     ,first_type
                     ,second_type_id
                     ,second_type_code
                     ,second_type
                     ,waybill_cnt
                     ,2 as network_type
                from cnt_base
                where financial_center_code is not null
            )agent1
        group by
            date_time,loss_type,network_code,first_type_id,first_type_code,second_type_id,second_type_code,network_type,first_type,second_type
    )tt left join jms_dim.dim_network_whole_massage net on net.code = tt.network_code
    distribute by 1 ;



